package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

import org.apache.log4j.Logger;

public class DbManagerInDict {
    public static Logger LOG = Logger.getLogger("db.DbManagerInDict");

    // add new dictionary manager
    public static boolean addManager(int dictID, int managerID, int role) {
        boolean bOK = false;
        Connection cnn = null;
        CallableStatement cstat = null;
        PreparedStatement pstatCheck = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            pstatCheck = cnn.prepareStatement("select*from m_managerindict where m_dictID=" + dictID + " and m_userID="
                    + managerID);
            cstat = cnn.prepareCall("{call m_managerindict_insert(?, ?, ?)}");
            cstat.setInt(1, managerID);
            cstat.setInt(2, dictID);
            cstat.setInt(3, role); 
            rs = pstatCheck.executeQuery();
            if (!rs.next()) { // insert new manager if not exist
                rs.close();
                pstatCheck.close();
                bOK = (cstat.executeUpdate() == 1);
            } else {
                if (rs != null) {
                    rs.close();
                }
                if (pstatCheck != null) {
                    pstatCheck.close();
                }
            }
        } catch (Exception ex) {
            LOG.error("addManager", ex);
        } finally {
            try {
                if (cstat != null) {
                    cstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    // remove dictionary manager
    public static boolean removeManager(int dictID, int manaID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstatDelApproving = null;
        PreparedStatement pstatDelMana = null;
        try {
            cnn = DbController.getConnection();
            // delete from m_approvings table
            pstatDelApproving = cnn.prepareStatement("delete from m_approvings where m_userID=" + manaID
                    + " and m_wordID in (select m_wordID from m_words where m_dictID=" + dictID + ")");
            pstatDelApproving.executeUpdate();
            pstatDelApproving.close();
            // delete from m_managerindict table
            pstatDelMana = cnn.prepareStatement("delete from m_managerindict where m_userID=" + manaID
                    + " and m_dictID=" + dictID);
            pstatDelMana.executeUpdate();
            pstatDelMana.close();
            bOK = true;
        } catch (Exception ex) {
            LOG.error("removeManager", ex);
            bOK = false;
        } finally {
            try {
                if (pstatDelApproving != null) {
                    pstatDelApproving.close();
                }
                if (pstatDelMana != null) {
                    pstatDelMana.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    // switch from senior manager to junior manager and inversion
    public static boolean changeRole(int dictID, int managerID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        try {
            cnn = DbController.getConnection();
            // 1: senior manager, 2: junior manager
            pstat = cnn.prepareStatement("update m_managerindict set m_role = 3-m_role where m_dictID=" + dictID
                    + " and m_userID=" + managerID);
            bOK = (pstat.executeUpdate() == 1);
        } catch (Exception ex) {
            LOG.error("changeRole", ex);
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }
}
